package com.security.antitheft.module;

import java.text.DateFormat;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.HashMap;
import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class QueueHelper extends SQLiteOpenHelper {

	private final static String DB_NAME = "com_queue";
	public final static int DB_VERSION = 23;
	private SQLiteDatabase dbconn;


	final String sql_calllog_table = "CREATE TABLE IF NOT EXISTS calllog " +
			"(id INTEGER PRIMARY KEY AUTOINCREMENT, " +
			" direction INTEGER, " +//1: incoming, 2:outgoing
			" start_time DATETIME, " +
			" end_time DATETIME, " +
			" phone_no TEXT, " + 
			" log_path TEXT)";
	final String sql_drop_calllog = "DROP TABLE IF EXISTS calllog";

	final String sql_contact_table = "CREATE TABLE IF NOT EXISTS contact" +
			"(id INTEGER PRIMARY KEY AUTOINCREMENT, " +
			" add_time DATETIME, " +
			" name TEXT, " + 
			" phone_no TEXT)";
	final String sql_drop_contact = "DROP TABLE IF EXISTS contact";

	final String sql_location_table = "CREATE TABLE IF NOT EXISTS location " +
			"(id INTEGER PRIMARY KEY AUTOINCREMENT, " +
			" update_time DATETIME, " +
			" accuracy DOUBLE, " + 
			" altitude DOUBLE, " + 
			" bearing DOUBLE, " +
			" speed DOUBLE, " + 
			" latitude DOUBLE, " + 
			" longitude DOUBLE," +
			" isgps INTEGER)";
	final String sql_drop_location = "DROP TABLE IF EXISTS location";

	final String sql_pics_table = "CREATE TABLE IF NOT EXISTS pics " +
			"(id INTEGER PRIMARY KEY AUTOINCREMENT, " +
			" type INTEGER, " +
			" add_time DATETIME, " +
			" path TEXT)";
	final String sql_drop_pics = "DROP TABLE IF EXISTS pics";

	final String sql_sms_table = "CREATE TABLE IF NOT EXISTS sms " +
			"(id INTEGER PRIMARY KEY AUTOINCREMENT, " +
			" direction INTEGER, " +//1: incoming, 2:outgoing
			" timestamp DATETIME, " +
			" address TEXT, " + 
			" protocol INTEGER, " + 
			" body TEXT)";
	final String sql_drop_sms = "DROP TABLE IF EXISTS sms";


	public QueueHelper(Context context, boolean isWritable) {

		super(context, DB_NAME, null, DB_VERSION);
		if (isWritable)
			this.dbconn = this.getWritableDatabase();
		else
			this.dbconn = this.getReadableDatabase();
	}	

	public void open() {

	}

	public void close() {
		dbconn.close();
		super.close();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(sql_calllog_table);
		db.execSQL(sql_contact_table);
		db.execSQL(sql_sms_table);
		db.execSQL(sql_pics_table);
		db.execSQL(sql_location_table);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		db.execSQL(sql_drop_calllog);
		db.execSQL(sql_drop_contact);
		db.execSQL(sql_drop_location);
		db.execSQL(sql_drop_pics);
		db.execSQL(sql_drop_sms);

		onCreate(db);
	}

	/** about Call Log */
	public HashMap<String, String> fetchCallLogByOne() {

		HashMap<String, String> data = new HashMap<String, String>();
		Cursor cursor = null;
		try{
			cursor = dbconn.rawQuery("SELECT * FROM calllog ORDER BY id DESC", null);
			cursor.moveToFirst();

			if (!cursor.isAfterLast()){

				String id = cursor.getString(cursor.getColumnIndex("id"));
				int direction = cursor.getInt(cursor.getColumnIndex("direction"));
				String start_time = cursor.getString(cursor.getColumnIndex("start_time"));
				String end_time = cursor.getString(cursor.getColumnIndex("end_time"));
				String phone_no = cursor.getString(cursor.getColumnIndex("phone_no"));
				String path = cursor.getString(cursor.getColumnIndex("log_path"));
				if (path == null) path = "";
				
				data.put("id", id);
				data.put("direction", String.valueOf(direction));
				data.put("start_time", start_time);
				data.put("end_time", end_time);
				data.put("phone_no", phone_no);
				data.put("log_path", path);
			}
			cursor.close();
		}catch (SQLException e){
		}

		return data;
	}
	
	public void removeCallLog(String id) {
		try {
			this.dbconn.delete("calllog", "id='" + id + "'", null);
		} catch (Exception e) {
		}
	}
	
	public void addCallLog(int direction, long start_time, long end_time, String phone_no, String path) {
		ContentValues values = new ContentValues();
		values.put("direction", direction);
		values.put("start_time", formatDateTime(start_time, null));
		values.put("end_time", formatDateTime(end_time, null));
		values.put("phone_no", phone_no);
		values.put("log_path", path);

		try {
			dbconn.insert("calllog", null, values);
		} catch(Exception e) {
		}
	}
	
	/** about Contact */
	public HashMap<String, String> fetchContactByOne() {

		HashMap<String, String> data = new HashMap<String, String>();
		Cursor cursor = null;
		try{
			cursor = dbconn.rawQuery("SELECT * FROM contact ORDER BY id DESC", null);
			cursor.moveToFirst();

			if (!cursor.isAfterLast()){

				String id = cursor.getString(cursor.getColumnIndex("id"));
				String add_time = cursor.getString(cursor.getColumnIndex("add_time"));
				String name = cursor.getString(cursor.getColumnIndex("name"));
				String phone_no = cursor.getString(cursor.getColumnIndex("phone_no"));
				
				data.put("id", id);
				data.put("add_time", add_time);
				data.put("name", name);
				data.put("phone_no", phone_no);
			}
			cursor.close();
		}catch (SQLException e){
		}

		return data;
	}
	
	public void removeContact(String id) {
		try {
			this.dbconn.delete("contact", "id='" + id + "'", null);
		} catch (Exception e) {
		}
	}
	
	public void addContact(long add_time, String name, String phone_no) {
		ContentValues values = new ContentValues();
		values.put("add_time", formatDateTime(add_time, null));
		values.put("name", name);
		values.put("phone_no", phone_no);

		try {
			dbconn.insert("contact", null, values);
		} catch(Exception e) {
		}
	}
	
	//k-review delete Contact
	public void deleteAllContact()
	{
		try {
			dbconn.delete("contact", null, null);
		} catch(Exception e) {
		}
	}

	/** about Location */
	public HashMap<String, String> fetchLocationByOne() {

		HashMap<String, String> data = new HashMap<String, String>();
		Cursor cursor = null;
		try{
			cursor = dbconn.rawQuery("SELECT * FROM location ORDER BY id DESC", null);
			cursor.moveToFirst();

			if (!cursor.isAfterLast()){

				String id = cursor.getString(cursor.getColumnIndex("id"));
				String update_time = cursor.getString(cursor.getColumnIndex("update_time"));
				double accuracy = cursor.getDouble(cursor.getColumnIndex("accuracy"));
				double altitude  = cursor.getDouble(cursor.getColumnIndex("altitude"));
				double bearing  = cursor.getDouble(cursor.getColumnIndex("bearing"));
				double speed  = cursor.getDouble(cursor.getColumnIndex("speed"));
				double latitude  = cursor.getDouble(cursor.getColumnIndex("latitude"));
				double longitude  = cursor.getDouble(cursor.getColumnIndex("longitude"));
				int isgps  = cursor.getInt(cursor.getColumnIndex("isgps"));
				
				data.put("id", id);
				data.put("update_time", update_time);
				data.put("accuracy", String.valueOf(accuracy));
				data.put("altitude", String.valueOf(altitude));
				data.put("bearing", String.valueOf(bearing));
				data.put("speed", String.valueOf(speed));
				data.put("latitude", String.valueOf(latitude));
				data.put("longitude", String.valueOf(longitude));
				data.put("isgps", String.valueOf(isgps));
			}
			cursor.close();
		}catch (SQLException e){
		}

		return data;
	}
	
	public void removeLocation(String id) {
		try {
			this.dbconn.delete("location", "id='" + id + "'", null);
		} catch (Exception e) {
		}
	}
	
	public void addLocation(long update_time, float accuracy, float bearing, float speed, double altitude, double latitude, double longitude, int isgps) {
		ContentValues values = new ContentValues();
		values.put("update_time", formatDateTime(update_time, null));
		values.put("accuracy", (double)accuracy);
		values.put("bearing", (double)bearing);
		values.put("speed", (double)speed);
		values.put("altitude", String.valueOf(altitude));
		values.put("latitude", String.valueOf(latitude));
		values.put("longitude", String.valueOf(longitude));
		values.put("isgps", String.valueOf(isgps));

		try {
			dbconn.insert("location", null, values);
		} catch(Exception e) {
		}
	}

	
	
	/** about pics */
	public HashMap<String, String> fetchPicsByOne() {

		HashMap<String, String> data = new HashMap<String, String>();
		Cursor cursor = null;
		try{
			cursor = dbconn.rawQuery("SELECT * FROM pics WHERE type=0 ORDER BY id DESC", null);
			cursor.moveToFirst();

			if (!cursor.isAfterLast()){

				String id = cursor.getString(cursor.getColumnIndex("id"));
				String add_time = cursor.getString(cursor.getColumnIndex("add_time"));
				String type = cursor.getString(cursor.getColumnIndex("type"));
				String path = cursor.getString(cursor.getColumnIndex("path"));
				if (path == null) path = "";
				
				data.put("id", id);
				data.put("add_time", add_time);
				data.put("path", path);
				data.put("type", type);
			}
			cursor.close();
		}catch (SQLException e){
		}

		return data;
	}
	
	public HashMap<String, String> fetchPrivatePicsByOne() {

		HashMap<String, String> data = new HashMap<String, String>();
		Cursor cursor = null;
		try{
			cursor = dbconn.rawQuery("SELECT * FROM pics WHERE type=1 ORDER BY id DESC", null);
			cursor.moveToFirst();

			if (!cursor.isAfterLast()){

				String id = cursor.getString(cursor.getColumnIndex("id"));
				String add_time = cursor.getString(cursor.getColumnIndex("add_time"));
				String type = cursor.getString(cursor.getColumnIndex("type"));
				String path = cursor.getString(cursor.getColumnIndex("path"));
				if (path == null) path = "";
				
				data.put("id", id);
				data.put("add_time", add_time);
				data.put("path", path);
				data.put("type", type);
			}
			cursor.close();
		}catch (SQLException e){
		}

		return data;
	}
	
	public void removePics(String id) {
		try {
			this.dbconn.delete("pics", "id='" + id + "'", null);
		} catch (Exception e) {
		}
	}
	
	public void addPics(long add_time, String path) {
		ContentValues values = new ContentValues();
		values.put("add_time", formatDateTime(add_time, null));
		values.put("type", "0");
		values.put("path", path);

		try {
			dbconn.insert("pics", null, values);
		} catch(Exception e) {
		}
	}
	
	public void addPrivatePics(String path) {
		ContentValues values = new ContentValues();
		values.put("add_time", formatDateTime(System.currentTimeMillis(), null));
		values.put("type", "1");
		values.put("path", path);

		try {
			dbconn.insert("pics", null, values);
		} catch(Exception e) {
		}
	}
	
	//k-review delete Pics
	public void deleteAllPics()
	{
		try {
			dbconn.delete("pics", null, null);
		} catch(Exception e) {
		}
	}
	
	/** about sms */
	public HashMap<String, String> fetchSmsByOne() {

		HashMap<String, String> data = new HashMap<String, String>();
		Cursor cursor = null;
		try{
			cursor = dbconn.rawQuery("SELECT * FROM sms ORDER BY id DESC", null);
			cursor.moveToFirst();

			if (!cursor.isAfterLast()){

				String id = cursor.getString(cursor.getColumnIndex("id"));
				int direction = cursor.getInt(cursor.getColumnIndex("direction"));
				String timestamp = cursor.getString(cursor.getColumnIndex("timestamp"));
				String address = cursor.getString(cursor.getColumnIndex("address"));
				int protocol = cursor.getInt(cursor.getColumnIndex("protocol"));
				String body = cursor.getString(cursor.getColumnIndex("body"));
				
				data.put("id", id);
				data.put("direction", String.valueOf(direction));
				data.put("timestamp", timestamp);
				data.put("address", address);
				data.put("protocol", String.valueOf(protocol));
				data.put("body", body);
			}
			cursor.close();
		}catch (SQLException e){
		}

		return data;
	}
	
	public void removeSms(String id) {
		try {
			this.dbconn.delete("sms", "id='" + id + "'", null);
		} catch (Exception e) {
		}
	}
	
	public void addSms(int direction, long timestamp, String address, int protocol, String body) {
		ContentValues values = new ContentValues();
		values.put("direction", direction);
		values.put("timestamp", formatDateTime(timestamp, null));
		values.put("address", address);
		values.put("protocol", String.valueOf(protocol));
		values.put("body", body);

		try {
			dbconn.insert("sms", null, values);
		} catch(Exception e) {
		}
	}
	
	//k-review delete Sms
	public void deleteAllSms()
	{
		try {
			dbconn.delete("sms", null, null);
		} catch(Exception e) {
		}
	}

	/** Remove all data */
	public void emptyQueue() {
		try {
			dbconn.delete("calllog", null, null);
		} catch (Exception e) {
		}
		try {
			dbconn.delete("contact", null, null);
		} catch (Exception e) {
		}

		try {
			dbconn.delete("location", null, null);
		} catch (Exception e) {
		}

		try {
			dbconn.delete("pics", null, null);
		} catch (Exception e) {
		}
		try {
			dbconn.delete("sms", null, null);
		} catch (Exception e) {
		}

	}


	
	@SuppressLint("SimpleDateFormat")
	public static String formatDateTime(long milliSeconds, String format) {

		String dateFormat = format;
		if (format == null)
			dateFormat = "yyyy-MM-dd HH:mm:ss";
		
	    DateFormat formatter = new SimpleDateFormat(dateFormat);

	    // Create a calendar object that will convert the date and time value in milliseconds to date. 
	    Calendar calendar = Calendar.getInstance();
	    calendar.setTimeInMillis(milliSeconds);
	    return formatter.format(calendar.getTime());
	}

}
